package day11.gec.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import day11.gec.bean.User;
import day11.gec.dao.UserDao;
import day11.gec.utils.JDBCUtil;
import day11.gec.utils.JDBCUtil2;

public class UserDaoImpl3 implements UserDao {

	@Override
	public void save(User user) {
		String sql = "insert into t_user(name,password)values(?,?)";

		Connection conn = null;
		PreparedStatement st = null;
		try {
			conn = JDBCUtil.getConnection();
			st = conn.prepareStatement(sql);
			// 给问号设置值
			st.setString(1, user.getName());
			st.setString(2, user.getPassword());

			// 执行语句
			st.executeUpdate();// 不要传sql进来

			System.out.println("执行的sql:" + st);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil2.closeConn(conn, st, null);
		}

	}

	@Override
	public void update(User user) {
		String sql = "update t_user set name = ?,password=? where id = ?";
		Connection conn = null;
		PreparedStatement st = null;
		try {
			conn = JDBCUtil.getConnection();
			st = conn.prepareStatement(sql);
			// 给问号设置值
			st.setString(1, user.getName());
			st.setString(2, user.getPassword());
			st.setInt(3, user.getId());

			// 执行语句
			st.executeUpdate();// 不要传sql进来

			// 打印sql
			System.out.println("执行的sql:" + st);

			System.out.println("修改成功...");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil2.closeConn(conn, st, null);
		}
	}

	@Override
	public void delete(User user) {
		String sql = "delete from t_user where id = ?";
		Connection conn = null;
		PreparedStatement st = null;
		try {
			conn = JDBCUtil.getConnection();
			st = conn.prepareStatement(sql);
			// 给问号设置值
			st.setInt(1, user.getId());
			// 执行语句
			st.executeUpdate();// 执行ddl dml

			// 执行打印的sql
			System.out.println("sql:" + st);

			System.out.println("删除成功....");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil2.closeConn(conn, st, null);
		}
	}

	@Override
	public User getUserById(int id) {
		String sql = "select * from t_user where id = ?";
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();
			st = conn.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			System.out.println("执行的sql:" + st);
			// 返回结果集 遍历
			while (rs.next()) {
				// 创建用户对象
				User user = new User();
				int uid = rs.getInt(1);
				String name = rs.getString("name");
				String password = rs.getString("password");

				user.setId(uid);
				user.setName(name);
				user.setPassword(password);

				// 返回用户信息
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil2.closeConn(conn, st, rs);
		}
		return null;
	}

	@Override
	public List<User> findUsers() {
		// 定义集合
		List<User> users = new ArrayList<>();
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			String sql = "select * from t_user";
			conn = JDBCUtil.getConnection();
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			// 返回结果集 遍历
			while (rs.next()) {
				// 创建用户对象
				User user = new User();
				int uid = rs.getInt(1);
				String name = rs.getString("name");
				String password = rs.getString("password");

				user.setId(uid);
				user.setName(name);
				user.setPassword(password);
				// 每个用户放到集合里面
				users.add(user);
			}

			return users;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil2.closeConn(conn, st, rs);
		}
		return null;
	}

}
